﻿<?xml version="1.0" encoding="utf-8" ?>
<!--namespace必须用否者就报错（读取配置文件报：未将对象引用设置到对象的实例）-->
<sqlMap namespace="" xmlns="http://ibatis.apache.org/mapping" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <statements>
    <insert id="InsertOrder" parameterClass="hashtable">
      insert into [Order]  values(#OrderName#,#OrderCode#,#OrderDesc#,#OrderCreateUserFullName#,#OrderCreateDateTime#)
    </insert>
    <insert id="InsertOrderDetail" parameterClass="hashtable">
      insert into OrderDetail  values(#OrderDetailCode#,#OrderProductCode#,#OrderProductIsHot#,#OrderDetailFullName#,#OrderCode#,#OrderCreateDateTime#)

    </insert>
    <insert id="InsertProduct" parameterClass="hashtable" resultClass="int">
      insert into Product  values(#ProductCode#,#ProductName#,#ProductPrice#,#ProductIsHot#)
    </insert>

    <update id="UpdateOrder" parameterClass="hashtable" restltClass="int">
      update [Order] set OrderName=#OrderName#,OrderDesc=#OrderDesc#,OrderCreateUserFullName=#OrderCreateUserFullName#,OrderCreateDateTime=#OrderCreateDateTime# where OrderCode=#OrderCode#
    </update>
    <update id="UpdateOrderDetail" parameterClass="hashtable" restltClass="int">
      update OrderDetail set OrderProductCode=#OrderProductCode#,OrderProductIsHot=#OrderProductIsHot#,OrderDetailFullName=#OrderDetailFullName# where OrderDetailCode=#OrderDetailCode#

    </update>
    <update id="UpdateProduct" parameterClass="hashtable" restltClass="int">
      update Product set ProductName=#ProductName#,ProductPrice=#ProductPrice#,ProductIsHot=#ProductIsHot# where ProductCode=#ProductCode#
    </update>

    <select id="SearchOrder" resultClass="HashTable">
      SELECT   *, Convert(varchar(4),DatePart(YYYY,OrderCreateDateTime))
      +'年' +Convert(varchar(4),DatePart(MM,OrderCreateDateTime))+ '月' +
      Convert(varchar(4),DatePart(DAY,OrderCreateDateTime))+'日' as OrderTime
      FROM [Order]
      <dynamic prepend="where">
        <isParameterPresent>
          <isNotEmpty prepend="and" property="OrderName">
            OrderName like '%$OrderName$%' or OrderCreateUserFullName like '%$OrderCreateUserFullName$%'
          </isNotEmpty>
        </isParameterPresent>
      </dynamic>
    </select>
    <select id="SearchOrderDetail" resultClass="HashTable">
      select tb1.OrderCode,OrderProductCode,OrderProductIsHot,OrderDetailCode,tb2.ProductName,
      case OrderProductIsHot when 1 then '热' when 0 then '冷' end as IsHot,
      OrderName,OrderDetailFullName,
      Convert(varchar(4),DatePart(YYYY,tb1.OrderCreateDateTime))
      +'年' +Convert(varchar(4),DatePart(MM,tb1.OrderCreateDateTime))+ '月' +
      Convert(varchar(4),DatePart(DAY,tb1.OrderCreateDateTime))+'日 '+
      Convert(varchar(9),tb1.OrderCreateDateTime,108) as OrderTime
      from OrderDetail tb1
      left join Product tb2 on tb1.OrderProductCode = tb2.ProductCode
      left join [Order] tb3 on tb1.OrderCode = tb3.OrderCode
    </select>
    <select id="SearchProduct" resultClass="HashTable">
      SELECT  *
      FROM    [Product]
      <dynamic prepend="where">
        <isParameterPresent>
          <isNotEmpty prepend="and" property="ProductName">
            ProductName like '%$ProductName$%' or ProductCode like '%$ProductName$%'
          </isNotEmpty>
        </isParameterPresent>
      </dynamic>
    </select>
    <select id="OrderDetailStatisticsList" resultClass="HashTable">
      SELECT   product.ProductName,COUNT(ProductName)as [Count]
      FROM [DCDB].[dbo].[Order],dbo.OrderDetail ,dbo.Product
      where [order].OrderCode=[OrderDetail].OrderCode and product.ProductCode=[OrderDetail].OrderProductCode
      <dynamic  >
        <isParameterPresent>
          <isNotEmpty prepend="and" property="OrderCode">
            OrderCode =  '$OrderCode$'
          </isNotEmpty>
        </isParameterPresent>
      </dynamic>

      group by ProductName
    </select>
    <select id="OrderStatisticsList" resultClass="HashTable">
      SELECT   *
      FROM  [Order]
    </select>

    <delete id="DeleteOrderList" parameterClass="string" resultClass="int">
      delete from [Order] where OrderCode in
      <iterate   property="ids" open="(" close=")"  conjunction=",">
        #ids[]#
      </iterate>
    </delete>
    <delete id="DeleteOrderDetailList" parameterClass="string" resultClass="int">
      delete from [OrderDetail] where OrderDetailCode in
      <iterate   property="ids" open="(" close=")"  conjunction=",">
        #ids[]#
      </iterate>
    </delete>
    <delete id="DeleteProductList" parameterClass="string" resultClass="int">
      delete from [Product] where ProductCode in
      <iterate   property="ids" open="(" close=")"  conjunction=",">
        #ids[]#
      </iterate>
    </delete>
  </statements>
</sqlMap>